<!DOCTYPE html><html><head><title>MySQL学习笔记（Day010：Employees/临时表的创建/外键约束）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day010employees临时表的创建外键约束">MySQL学习笔记（Day010：Employees/临时表的创建/外键约束）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day010employees临时表的创建外键约束">MySQL学习笔记（Day010：Employees/临时表的创建/外键约束）</a><ul>
<li><a href="#一-employees数据库安装">一. Employees数据库安装</a><ul>
<li><a href="#1-employees数据库介绍">1. Employees数据库介绍</a></li>
<li><a href="#2-employees的安装">2. Employees的安装</a><ul>
<li><a href="#21-下载">2.1. 下载</a></li>
<li><a href="#22-解压和拉取">2.2. 解压和拉取</a></li>
<li><a href="#23-安装">2.3. 安装</a></li>
<li><a href="#24-验证">2.4. 验证</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#二-表table">二. 表(TABLE)</a><ul>
<li><a href="#1-表的介绍">1. 表的介绍</a></li>
<li><a href="#2-表是数据的集合">2. 表是数据的集合</a></li>
<li><a href="#3-创建表">3. 创建表</a><ul>
<li><a href="#31-临时表">3.1. 临时表</a></li>
</ul>
</li>
<li><a href="#4-查看表结构">4. 查看表结构</a></li>
<li><a href="#5-alter-table">5. ALTER TABLE</a></li>
</ul>
</li>
<li><a href="#三-外键约束">三. 外键约束</a><ul>
<li><a href="#1-外键的介绍">1. 外键的介绍</a></li>
<li><a href="#2-外键操作">2. 外键操作</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-employees数据库安装">一. Employees数据库安装</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-employees数据库介绍">1. Employees数据库介绍</h3>

<p><code>Employees</code>数据库是一个用于学习和测试的数据库，大约<code>160MB</code>，<code>4百万条记录</code></p>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-employees的安装">2. Employees的安装</h3>

<blockquote>
  <p><a href="https://dev.mysql.com/doc/employee/en/employees-installation.html" target="_blank">官方安装文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<h4 id="21-下载">2.1. 下载</h4>

<p>根据官方文档的连接，我们可以找到下载该数据库的两种方式</p>

<ul><li><a href="https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2" target="_blank">employees_db-full-1.0.6.tar.bz2</a></li>
<li><a href="https://github.com/datacharmer/test_db" target="_blank">github-test_db</a> 使用<code>git clone</code> 进行仓库拉取</li>
</ul>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h4 id="22-解压和拉取">2.2. 解压和拉取</h4>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[root@MyServer Downloads]&gt; tar jxf employees_db-full-<span class="hljs-number">1.0</span>.<span class="hljs-number">6</span>.tar.bz2 <br><span class="hljs-comment"># 老师讲解时说是解压有问题，但是我这里却可以解压</span><br>[root@MyServer Downloads]&gt; <span class="hljs-built_in">cd</span> employees_db<br>[root@MyServer employees_db]&gt; ll<br>total <span class="hljs-number">164492</span><br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games       <span class="hljs-number">752</span> Mar <span class="hljs-number">30</span>  <span class="hljs-number">2009</span> Changelog<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">6460</span> Oct  <span class="hljs-number">9</span>  <span class="hljs-number">2008</span> employees_partitioned2.sql<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">7624</span> Feb  <span class="hljs-number">6</span>  <span class="hljs-number">2009</span> employees_partitioned3.sql<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">5660</span> Feb  <span class="hljs-number">6</span>  <span class="hljs-number">2009</span> employees_partitioned.sql<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">3861</span> Nov <span class="hljs-number">28</span>  <span class="hljs-number">2008</span> employees.sql <span class="hljs-comment"># 主要文件</span><br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games       <span class="hljs-number">241</span> Jul <span class="hljs-number">30</span>  <span class="hljs-number">2008</span> load_departments.dump<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games  <span class="hljs-number">13828291</span> Mar <span class="hljs-number">30</span>  <span class="hljs-number">2009</span> load_dept_emp.dump<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">1043</span> Jul <span class="hljs-number">30</span>  <span class="hljs-number">2008</span> load_dept_manager.dump<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games  <span class="hljs-number">17422825</span> Jul <span class="hljs-number">30</span>  <span class="hljs-number">2008</span> load_employees.dump<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games <span class="hljs-number">115848997</span> Jul <span class="hljs-number">30</span>  <span class="hljs-number">2008</span> load_salaries.dump<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games  <span class="hljs-number">21265449</span> Jul <span class="hljs-number">30</span>  <span class="hljs-number">2008</span> load_titles.dump<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">3889</span> Mar <span class="hljs-number">30</span>  <span class="hljs-number">2009</span> objects.sql<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">2211</span> Jul <span class="hljs-number">30</span>  <span class="hljs-number">2008</span> README<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">4455</span> Mar <span class="hljs-number">30</span>  <span class="hljs-number">2009</span> <span class="hljs-built_in">test</span>_employees_md5.sql<br>-rw-r--r--. <span class="hljs-number">1</span> <span class="hljs-number">501</span> games      <span class="hljs-number">4450</span> Mar <span class="hljs-number">30</span>  <span class="hljs-number">2009</span> <span class="hljs-built_in">test</span>_employees_sha.sql<br><br><br>[root@MyServer github_employees]&gt; git <span class="hljs-built_in">clone</span> https://github.com/datacharmer/<span class="hljs-built_in">test</span>_db.git<br>Initialized empty Git repository <span class="hljs-keyword">in</span> /root/Downloads/github_employees/<span class="hljs-built_in">test</span>_db/.git/<br>Cloning into <span class="hljs-string">'test_db'</span>...<br>remote: Counting objects: <span class="hljs-number">94</span>, done.<br>remote: Total <span class="hljs-number">94</span> (delta <span class="hljs-number">0</span>), reused <span class="hljs-number">0</span> (delta <span class="hljs-number">0</span>), pack-reused <span class="hljs-number">94</span><br>Unpacking objects: <span class="hljs-number">100</span>% (<span class="hljs-number">94</span>/<span class="hljs-number">94</span>), done.<br>Checking connectivity... done.<br><br>[root@MyServer <span class="hljs-built_in">test</span>_db]&gt; ll<br>total <span class="hljs-number">168340</span><br>-rw-r--r--. <span class="hljs-number">1</span> root root      <span class="hljs-number">964</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> Changelog<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">7948</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> employees_partitioned_5.<span class="hljs-number">1</span>.sql<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">6276</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> employees_partitioned.sql<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">4193</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> employees.sql  <span class="hljs-comment"># 主要文件</span><br>drwxrwxr-x. <span class="hljs-number">2</span> root root     <span class="hljs-number">4096</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> images<br>-rw-r--r--. <span class="hljs-number">1</span> root root      <span class="hljs-number">250</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_departments.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root <span class="hljs-number">14159880</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_dept_emp.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">1090</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_dept_manager.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root <span class="hljs-number">17722832</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_employees.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root <span class="hljs-number">39806034</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_salaries1.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root <span class="hljs-number">39805981</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_salaries2.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root <span class="hljs-number">39080916</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_salaries3.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root <span class="hljs-number">21708736</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> load_titles.dump<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">4568</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> objects.sql<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">4007</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> README.md<br>drwxrwxr-x. <span class="hljs-number">2</span> root root     <span class="hljs-number">4096</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> sakila<br>-rw-r--r--. <span class="hljs-number">1</span> root root      <span class="hljs-number">272</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> show_elapsed.sql<br>-rwxr-xr-x. <span class="hljs-number">1</span> root root     <span class="hljs-number">1800</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> sql_test.sh<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">4878</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> <span class="hljs-built_in">test</span>_employees_md5.sql<br>-rw-r--r--. <span class="hljs-number">1</span> root root     <span class="hljs-number">4882</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">25</span> <span class="hljs-built_in">test</span>_employees_sha.sql<br></code></pre>

<p><strong>解压出来的主要文件大小是不一样的，且根据网页上发布和更新的时间上看，github中的时间比较新，所以这里使用github中源作为的安装文件</strong></p>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<h4 id="23-安装">2.3. 安装</h4>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[root@MyServer <span class="hljs-built_in">test</span>_db]&gt; mysql -uroot -S /tmp/mysql.sock_57 -p &lt; employees.sql <br>Enter password: <br>INFO<br>CREATING DATABASE STRUCTURE<br>INFO<br>storage engine: InnoDB<br>INFO<br>LOADING departments<br>INFO<br>LOADING employees<br>INFO<br>LOADING dept_emp<br>INFO<br>LOADING dept_manager<br>INFO<br>LOADING titles<br>INFO<br>LOADING salaries<br>data_load_time_diff<br><span class="hljs-number">00</span>:<span class="hljs-number">01</span>:<span class="hljs-number">51</span><br></code></pre>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h4 id="24-验证">2.4. 验证</h4>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[root@MyServer <span class="hljs-built_in">test</span>_db]&gt; time mysql -uroot -S /tmp/mysql.sock_57 -p -t &lt; <span class="hljs-built_in">test</span>_employees_sha.sql <br>Enter password: <br>+----------------------+<br>| INFO                 |<br>+----------------------+<br>| TESTING INSTALLATION |<br>+----------------------+<br>+--------------+------------------+------------------------------------------+<br>| table_name   | expected_records | expected_crc                             |<br>+--------------+------------------+------------------------------------------+<br>| employees    |           <span class="hljs-number">300024</span> | <span class="hljs-number">4</span>d4aa689914d8fd41db7e45c2168e7dcb9697359 |<br>| departments  |                <span class="hljs-number">9</span> | <span class="hljs-number">4</span>b315afa0e35ca6649df897b958345bcb3d2b764 |<br>| dept_manager |               <span class="hljs-number">24</span> | <span class="hljs-number">9687</span>a7d6f93ca8847388a42a6d8d93982a841c6c |<br>| dept_emp     |           <span class="hljs-number">331603</span> | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |<br>| titles       |           <span class="hljs-number">443308</span> | d12d5f746b88f07e69b9e36675b6067abb01b60e |<br>| salaries     |          <span class="hljs-number">2844047</span> | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |<br>+--------------+------------------+------------------------------------------+<br>+--------------+------------------+------------------------------------------+<br>| table_name   | found_records    | found_crc                                |<br>+--------------+------------------+------------------------------------------+<br>| employees    |           <span class="hljs-number">300024</span> | <span class="hljs-number">4</span>d4aa689914d8fd41db7e45c2168e7dcb9697359 |<br>| departments  |                <span class="hljs-number">9</span> | <span class="hljs-number">4</span>b315afa0e35ca6649df897b958345bcb3d2b764 |<br>| dept_manager |               <span class="hljs-number">24</span> | <span class="hljs-number">9687</span>a7d6f93ca8847388a42a6d8d93982a841c6c |<br>| dept_emp     |           <span class="hljs-number">331603</span> | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |<br>| titles       |           <span class="hljs-number">443308</span> | d12d5f746b88f07e69b9e36675b6067abb01b60e |<br>| salaries     |          <span class="hljs-number">2844047</span> | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |<br>+--------------+------------------+------------------------------------------+<br>+--------------+---------------+-----------+<br>| table_name   | records_match | crc_match |<br>+--------------+---------------+-----------+<br>| employees    | OK            | ok        |<br>| departments  | OK            | ok        |<br>| dept_manager | OK            | ok        |<br>| dept_emp     | OK            | ok        |<br>| titles       | OK            | ok        |<br>| salaries     | OK            | ok        |<br>+--------------+---------------+-----------+<br>+------------------+<br>| computation_time |<br>+------------------+<br>| <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">18</span>         |<br>+------------------+<br>+---------+--------+<br>| summary | result |<br>+---------+--------+<br>| CRC     | OK     |<br>| count   | OK     |<br>+---------+--------+<br><br>real    <span class="hljs-number">0</span>m19.<span class="hljs-number">406</span>s<br>user    <span class="hljs-number">0</span>m0.<span class="hljs-number">005</span>s<br>sys     <span class="hljs-number">0</span>m0.<span class="hljs-number">004</span>s<br><br><br>[root@MyServer <span class="hljs-built_in">test</span>_db]&gt; time mysql -uroot -S /tmp/mysql.sock_57 -p -t &lt; <span class="hljs-built_in">test</span>_employees_md5.sql    <br>Enter password: <br>+----------------------+<br>| INFO                 |<br>+----------------------+<br>| TESTING INSTALLATION |<br>+----------------------+<br>+--------------+------------------+----------------------------------+<br>| table_name   | expected_records | expected_crc                     |<br>+--------------+------------------+----------------------------------+<br>| employees    |           <span class="hljs-number">300024</span> | <span class="hljs-number">4</span>ec56ab5ba37218d187cf6ab09ce1aa1 |<br>| departments  |                <span class="hljs-number">9</span> | d1af5e170d2d1591d776d5638d71<span class="hljs-built_in">fc</span>5f |<br>| dept_manager |               <span class="hljs-number">24</span> | <span class="hljs-number">8720</span>e2f0853ac9096b689c14664f847e |<br>| dept_emp     |           <span class="hljs-number">331603</span> | ccf6fe516f990bdaa49713<span class="hljs-built_in">fc</span>478701b7 |<br>| titles       |           <span class="hljs-number">443308</span> | bfa016c472df68e70a03facafa1bc0a8 |<br>| salaries     |          <span class="hljs-number">2844047</span> | fd220654e95aea1b169624ffe3fca934 |<br>+--------------+------------------+----------------------------------+<br>+--------------+------------------+----------------------------------+<br>| table_name   | found_records    | found_crc                        |<br>+--------------+------------------+----------------------------------+<br>| employees    |           <span class="hljs-number">300024</span> | <span class="hljs-number">4</span>ec56ab5ba37218d187cf6ab09ce1aa1 |<br>| departments  |                <span class="hljs-number">9</span> | d1af5e170d2d1591d776d5638d71<span class="hljs-built_in">fc</span>5f |<br>| dept_manager |               <span class="hljs-number">24</span> | <span class="hljs-number">8720</span>e2f0853ac9096b689c14664f847e |<br>| dept_emp     |           <span class="hljs-number">331603</span> | ccf6fe516f990bdaa49713<span class="hljs-built_in">fc</span>478701b7 |<br>| titles       |           <span class="hljs-number">443308</span> | bfa016c472df68e70a03facafa1bc0a8 |<br>| salaries     |          <span class="hljs-number">2844047</span> | fd220654e95aea1b169624ffe3fca934 |<br>+--------------+------------------+----------------------------------+<br>+--------------+---------------+-----------+<br>| table_name   | records_match | crc_match |<br>+--------------+---------------+-----------+<br>| employees    | OK            | ok        |<br>| departments  | OK            | ok        |<br>| dept_manager | OK            | ok        |<br>| dept_emp     | OK            | ok        |<br>| titles       | OK            | ok        |<br>| salaries     | OK            | ok        |<br>+--------------+---------------+-----------+<br>+------------------+<br>| computation_time |<br>+------------------+<br>| <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">16</span>         |<br>+------------------+<br>+---------+--------+<br>| summary | result |<br>+---------+--------+<br>| CRC     | OK     |<br>| count   | OK     |<br>+---------+--------+<br><br>real    <span class="hljs-number">0</span>m18.<span class="hljs-number">452</span>s<br>user    <span class="hljs-number">0</span>m0.<span class="hljs-number">007</span>s<br>sys     <span class="hljs-number">0</span>m0.<span class="hljs-number">005</span>s<br></code></pre>

<p>至此，<code>Employees</code>测试数据库就安装完成了</p>

<hr>

</div><div id="wmd-preview-section-8833" class="wmd-preview-section preview-content">

<h2 id="二-表table">二. 表(TABLE)</h2></div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h3 id="1-表的介绍">1. 表的介绍</h3>

<ul><li>表是关系数据库的核心</li>
<li>表 = 关系</li>
<li>表是记录的集合</li>
<li>二维表格模型易于人的理解</li>
<li>MySQL默认存储引擎都是基于行(记录)存储</li>
<li>每行记录都是基于列进行组织的</li>
</ul>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<h3 id="2-表是数据的集合">2. 表是数据的集合</h3>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table_name <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</span>  <br></code></pre>

<p>集合是无序的，上面的SQL语句的意思是 <strong>从表(集合)中<code>随机</code>选出一条数据，结果是不确定的</strong>, 不能简单的认为是取出第一条数据。</p>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table_name <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> col_name <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</span><br></code></pre>

<p>只有通过<code>order by</code>排序之后取出的数据，才是确定的。</p>

</div><div id="wmd-preview-section-9067" class="wmd-preview-section preview-content">

<h3 id="3-创建表">3. 创建表</h3></div><div id="wmd-preview-section-8981" class="wmd-preview-section preview-content">

<h4 id="31-临时表">3.1. 临时表</h4>

<blockquote>
  <p><a href="https://dev.mysql.com/doc/refman/5.7/en/create-table.html" target="_blank">官方文档 表创建的语法</a></p>
</blockquote>

<ul><li><strong>临时表的创建</strong></li>
</ul></div><div id="wmd-preview-section-19" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">--  mysql 5.7.9</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">use</span> burn_test;</span><br>Database changed<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">temporary</span> <span class="hljs-keyword">table</span> temp_a(a <span class="hljs-built_in">int</span>);</span><br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span>  <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> temp_a\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>       <span class="hljs-keyword">Table</span>: temp_a<br><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TEMPORARY</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`temp_a`</span> (<br>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4   <span class="hljs-comment">-- 使用的是innodb </span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql 5.6.27</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">------------+</span><br>| version()  |<br>+<span class="hljs-comment">------------+</span><br>| 5.6.27-log |<br>+<span class="hljs-comment">------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">temporary</span> <span class="hljs-keyword">table</span>  temp_a_56(a <span class="hljs-built_in">int</span>);</span><br>Query OK, 0 rows affected (0.06 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> temp_a_56\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>       <span class="hljs-keyword">Table</span>: temp_a_56<br><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TEMPORARY</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`temp_a_56`</span> (<br>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4  <span class="hljs-comment">-- 5.6 也是innodb</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-----</span><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 终端1 MySQL 5.7.9</span><br><span class="hljs-comment">--</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">processlist</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>     Id: <span class="hljs-number">10</span>  <span class="hljs-comment">-- 当前ID 是 10</span><br>   <span class="hljs-keyword">User</span>: root<br>   Host: localhost<br>     db: burn_test<br>Command: <span class="hljs-keyword">Query</span><br>   <span class="hljs-keyword">Time</span>: <span class="hljs-number">0</span><br>  State: <span class="hljs-keyword">starting</span><br>   Info: <span class="hljs-keyword">show</span> <span class="hljs-keyword">processlist</span>   <span class="hljs-comment">-- 当前终端执行</span><br>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br>     Id: <span class="hljs-number">12</span><br>   <span class="hljs-keyword">User</span>: root<br>   Host: localhost<br>     db: <span class="hljs-literal">NULL</span><br>Command: <span class="hljs-keyword">Sleep</span><br>   <span class="hljs-keyword">Time</span>: <span class="hljs-number">328</span><br>  State: <br>   Info: <span class="hljs-literal">NULL</span><br>*************************** <span class="hljs-number">3.</span> <span class="hljs-keyword">row</span> ***************************<br>     Id: <span class="hljs-number">13</span><br>   <span class="hljs-keyword">User</span>: root<br>   Host: localhost<br>     db: burn_test<br>Command: <span class="hljs-keyword">Sleep</span><br>   <span class="hljs-keyword">Time</span>: <span class="hljs-number">16</span><br>  State: <br>   Info: <span class="hljs-literal">NULL</span><br><span class="hljs-number">3</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> temp_a <span class="hljs-keyword">values</span>(<span class="hljs-number">123</span>);</span><br>Query OK, 1 row affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> temp_a;</span>      <br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>|  123 |<br>+<span class="hljs-comment">------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 终端2 MySQL 5.7.9</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">processlist</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>     Id: <span class="hljs-number">10</span><br>   <span class="hljs-keyword">User</span>: root<br>   Host: localhost<br>     db: burn_test<br>Command: <span class="hljs-keyword">Sleep</span><br>   <span class="hljs-keyword">Time</span>: <span class="hljs-number">75</span><br>  State: <br>   Info: <span class="hljs-literal">NULL</span><br>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br>     Id: <span class="hljs-number">12</span><br>   <span class="hljs-keyword">User</span>: root<br>   Host: localhost<br>     db: <span class="hljs-literal">NULL</span><br>Command: <span class="hljs-keyword">Sleep</span><br>   <span class="hljs-keyword">Time</span>: <span class="hljs-number">403</span><br>  State: <br>   Info: <span class="hljs-literal">NULL</span><br>*************************** <span class="hljs-number">3.</span> <span class="hljs-keyword">row</span> ***************************<br>     Id: <span class="hljs-number">13</span>   <span class="hljs-comment">-- 当前 ID 是 13</span><br>   <span class="hljs-keyword">User</span>: root<br>   Host: localhost<br>     db: burn_test<br>Command: <span class="hljs-keyword">Query</span><br>   <span class="hljs-keyword">Time</span>: <span class="hljs-number">0</span><br>  State: <span class="hljs-keyword">starting</span><br>   Info: <span class="hljs-keyword">show</span> <span class="hljs-keyword">processlist</span>  <span class="hljs-comment">-- 当前终端执行</span><br><span class="hljs-number">3</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">use</span> burn_test;</span><br>Database changed<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">tables</span>;</span><br>Empty <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)   <span class="hljs-comment">-- 从其他终端登录的用户(session)无法看到temp_a这个临时表</span><br><br><span class="hljs-comment">----</span><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 临时表 和 普通表 同名问题</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_1 (a <span class="hljs-built_in">int</span>);</span>  <span class="hljs-comment">-- 创建一张普通的表叫做 test_1</span><br>Query OK, 0 rows affected (0.16 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_1 <span class="hljs-keyword">values</span>(<span class="hljs-number">23</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_1 <span class="hljs-keyword">values</span>(<span class="hljs-number">24</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_1;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>|   23 |   <span class="hljs-comment">-- 可以看到插入的数据</span><br>|   24 |<br>+<span class="hljs-comment">------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">temporary</span> <span class="hljs-keyword">table</span> test_1 (a <span class="hljs-built_in">int</span>);</span>   <span class="hljs-comment">-- 创建一种和test_1 同名的临时表</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_1 <span class="hljs-keyword">values</span>(<span class="hljs-number">1000</span>);</span>  <span class="hljs-comment">-- 插入一个 不一样的值</span><br>Query OK, 1 row affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_1;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>| 1000 |   <span class="hljs-comment">-- 只能搜索到 临时表中的数据</span><br>+<span class="hljs-comment">------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">temporary</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">if</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> table_name  (a <span class="hljs-built_in">int</span>);</span>  <span class="hljs-comment">-- 使用if not exists进行判断</span><br></code></pre>

<blockquote>
  <p><strong>1：临时表是<code>SESSION</code>级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的</strong> <br>
  <strong>2：当临时表和普通表同名时，当前用户只能看到同名的临时表</strong> <br>
  <strong>3：创建表时带上<code>if not exists</code>进行表的存在性检查；同时建议在临时表的表名前面加上统一的<code>prefix</code></strong></p>
</blockquote>

<ul><li><p><strong>临时表的作用</strong></p>

<ul>
<li>临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。</li>
<li>不要和SQL优化器在排序过程中内部帮你创建的临时表相混淆。</li></ul></li>
<li><p><strong>临时表的存储引擎</strong></p></li>
</ul>

</div><div id="wmd-preview-section-20" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"default%tmp%"</span>;</span><br>+<span class="hljs-comment">----------------------------+--------+</span><br>| Variable_name              | Value  |<br>+<span class="hljs-comment">----------------------------+--------+</span><br>| default_tmp_storage_engine | InnoDB |  <span class="hljs-comment">-- 5.7.9 的临时表默认存储引擎就是 InnoDB</span><br>+<span class="hljs-comment">----------------------------+--------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">------------+</span><br>| version()  |<br>+<span class="hljs-comment">------------+</span><br>| 5.6.27-log |<br>+<span class="hljs-comment">------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"default%tmp%"</span>;</span><br>+<span class="hljs-comment">----------------------------+--------+</span><br>| Variable_name              | Value  |<br>+<span class="hljs-comment">----------------------------+--------+</span><br>| default_tmp_storage_engine | InnoDB |  <span class="hljs-comment">-- 5.7.26 的临时表默认存储引擎也是 InnoDB</span><br>+<span class="hljs-comment">----------------------------+--------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- 5.6 之前用的是MyISAM</span></span><br></code></pre>

<ul><li><strong>临时表存储位置</strong></li>
</ul>

</div><div id="wmd-preview-section-525" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># MySQL 5.7</span><br><span class="hljs-comment">#</span><br>mysql&gt; system ls <span class="hljs-operator">-l</span> /tmp  <span class="hljs-comment"># 使用system 可以解析执行linux shell命令</span><br>total <span class="hljs-number">20</span><br>drwxr-xr-x. <span class="hljs-number">4</span> mysql mysql <span class="hljs-number">4096</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">10</span>:<span class="hljs-number">06</span> mysql_data<br>srwxrwxrwx. <span class="hljs-number">1</span> mysql mysql    <span class="hljs-number">0</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">20</span> mysql.sock_56<br>srwxrwxrwx. <span class="hljs-number">1</span> mysql mysql    <span class="hljs-number">0</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">20</span>:<span class="hljs-number">51</span> mysql.sock_57<br>-rw-------. <span class="hljs-number">1</span> mysql mysql    <span class="hljs-number">5</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">20</span>:<span class="hljs-number">51</span> mysql.sock_57.lock<br>-rw-r-----. <span class="hljs-number">1</span> mysql mysql <span class="hljs-number">8554</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">22</span>:<span class="hljs-number">04</span> <span class="hljs-comment">#sqlf18_a_0.frm  -- temp_1 的表结构</span><br><br>mysql&gt; system ls <span class="hljs-operator">-l</span> /data/mysql_data/<span class="hljs-number">5.7</span>/ | grep ib<br>-rw-r-----. <span class="hljs-number">1</span> mysql mysql       <span class="hljs-number">879</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">20</span>:<span class="hljs-number">47</span> ib_buffer_pool<br>-rw-r-----. <span class="hljs-number">1</span> mysql mysql  <span class="hljs-number">12582912</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">22</span>:<span class="hljs-number">21</span> ibdata1<br>-rw-r-----. <span class="hljs-number">1</span> mysql mysql <span class="hljs-number">134217728</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">22</span>:<span class="hljs-number">20</span> ib_logfile0<br>-rw-r-----. <span class="hljs-number">1</span> mysql mysql <span class="hljs-number">134217728</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">33</span> ib_logfile1<br>-rw-r-----. <span class="hljs-number">1</span> mysql mysql  <span class="hljs-number">12582912</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">22</span>:<span class="hljs-number">33</span> ibtmp1  -- 这个是我们的表结构对应的数据<br><br>mysql&gt; show variables like <span class="hljs-string">"innodb_temp%"</span>;<br>+----------------------------+-----------------------+<br>| Variable_name              | Value                 |<br>+----------------------------+-----------------------+<br>| innodb_temp_data_file_path | ibtmp1:<span class="hljs-number">12</span>M:autoextend |<br>+----------------------------+-----------------------+<br><span class="hljs-number">1</span> row <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">#-----</span><br><br><span class="hljs-comment">#</span><br><span class="hljs-comment"># MySQL 5.6</span><br><span class="hljs-comment">#</span><br>mysql&gt; system ls <span class="hljs-operator">-l</span> /tmp<br>total <span class="hljs-number">68</span><br>drwxr-xr-x. <span class="hljs-number">4</span> mysql mysql  <span class="hljs-number">4096</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">10</span>:<span class="hljs-number">06</span> mysql_data<br>srwxrwxrwx. <span class="hljs-number">1</span> mysql mysql     <span class="hljs-number">0</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">21</span>:<span class="hljs-number">20</span> mysql.sock_56<br>srwxrwxrwx. <span class="hljs-number">1</span> mysql mysql     <span class="hljs-number">0</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">20</span>:<span class="hljs-number">51</span> mysql.sock_57<br>-rw-------. <span class="hljs-number">1</span> mysql mysql     <span class="hljs-number">5</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">20</span>:<span class="hljs-number">51</span> mysql.sock_57.lock<br>-rw-rw----. <span class="hljs-number">1</span> mysql mysql  <span class="hljs-number">8554</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">22</span>:<span class="hljs-number">38</span> <span class="hljs-comment">#sql13f3_7_0.frm   -- 表结构</span><br>-rw-rw----. <span class="hljs-number">1</span> mysql mysql <span class="hljs-number">49152</span> Dec  <span class="hljs-number">2</span> <span class="hljs-number">22</span>:<span class="hljs-number">38</span> <span class="hljs-comment">#sql13f3_7_0.ibd   -- 表数据</span><br><br><span class="hljs-comment"># 5.6.27 中没有 innodb_temp_data_file_path 变量</span><br>mysql&gt; show variables like <span class="hljs-string">"innodb_temp%"</span>;<br>Empty <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; show variables like <span class="hljs-string">"%innodb%temp%"</span>;<br>Empty <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)<br></code></pre>

<blockquote>
  <p>MySQL5.7.9 把临时<code>表结构</code>放在<code>tmpdir</code>，而数据<code>表数据</code>放在<code>datadir</code> <br>
  MySQL5.6.27 把临时<code>表结构</code>和<code>表数据</code>都放在<code>tmpdir</code></p>
</blockquote></div><div id="wmd-preview-section-554" class="wmd-preview-section preview-content">

<h3 id="4-查看表结构">4. 查看表结构</h3></div><div id="wmd-preview-section-9160" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_1\G   <span class="hljs-comment">-- 表结构</span><br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>       <span class="hljs-keyword">Table</span>: test_1<br><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_1`</span> (<br>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4<br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">desc</span> test_1\G  <span class="hljs-comment">-- 表的描述,描述二维表信息</span><br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>  <span class="hljs-keyword">Field</span>: a<br>   Type: <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>)<br>   <span class="hljs-literal">Null</span>: YES<br>    <span class="hljs-keyword">Key</span>: <br><span class="hljs-keyword">Default</span>: <span class="hljs-literal">NULL</span><br>  Extra: <br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">status</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"test_1"</span>\G    <span class="hljs-comment">-- 看表结构的元数据信息</span><br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>           Name: test_1<br>         <span class="hljs-keyword">Engine</span>: <span class="hljs-keyword">InnoDB</span><br>        <span class="hljs-keyword">Version</span>: <span class="hljs-number">10</span><br>     Row_format: Dynamic<br>           <span class="hljs-keyword">Rows</span>: <span class="hljs-number">2</span><br> Avg_row_length: <span class="hljs-number">4096</span><br>    Data_length: <span class="hljs-number">8192</span><br>Max_data_length: <span class="hljs-number">0</span><br>   Index_length: <span class="hljs-number">0</span><br>      Data_free: <span class="hljs-number">0</span><br> Auto_increment: <span class="hljs-literal">NULL</span><br>    Create_time: <span class="hljs-number">2015</span>-<span class="hljs-number">12</span>-<span class="hljs-number">02</span> <span class="hljs-number">22</span>:<span class="hljs-number">20</span>:<span class="hljs-number">19</span><br>    Update_time: <span class="hljs-number">2015</span>-<span class="hljs-number">12</span>-<span class="hljs-number">02</span> <span class="hljs-number">22</span>:<span class="hljs-number">20</span>:<span class="hljs-number">44</span><br>     Check_time: <span class="hljs-literal">NULL</span><br>      <span class="hljs-keyword">Collation</span>: utf8mb4_general_ci<br>       <span class="hljs-keyword">Checksum</span>: <span class="hljs-literal">NULL</span><br> Create_options: <br>        Comment: <br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br></span><br></code></pre>

</div><div id="wmd-preview-section-10267" class="wmd-preview-section preview-content">

<h3 id="5-alter-table">5. ALTER TABLE</h3>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/alter-table.html" target="_blank">ALTER TABLE语法官方文档</a></p>
</blockquote></div><div id="wmd-preview-section-13492" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> test_1 <span class="hljs-keyword">add</span> <span class="hljs-keyword">column</span> b <span class="hljs-built_in">char</span>(<span class="hljs-number">10</span>);</span>  <span class="hljs-comment">-- 添加列 b</span><br>Query OK, 0 rows affected (0.25 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_1;</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|   23 | NULL |<br>|   24 | NULL |<br>+<span class="hljs-comment">------+------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> test_1 <span class="hljs-keyword">drop</span> <span class="hljs-keyword">column</span> b;</span>  <span class="hljs-comment">-- 删除列 b</span><br>Query OK, 0 rows affected (0.27 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_1;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>|   23 |<br>|   24 |<br>+<span class="hljs-comment">------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>注意，当表记录很大的时候，<code>alter table</code>会很耗时，影响性能</p>
</blockquote>

<ul><li><strong>ONLINE DDL</strong> <br>
5.6以后对在线DDL操作进行了优化，以提高性能。<a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html" target="_blank">官方文档</a>     </li>
</ul>

<hr></div><div id="wmd-preview-section-13152" class="wmd-preview-section preview-content">

<h2 id="三-外键约束">三. 外键约束</h2>

</div><div id="wmd-preview-section-13153" class="wmd-preview-section preview-content">

<h3 id="1-外键的介绍">1. 外键的介绍</h3>

<blockquote>
  <p><a href="https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html" target="_blank">官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-13154" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 摘自 MySQL官方文档</span><br><span class="hljs-comment">--</span><br><br><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> product (         <span class="hljs-comment">-- 商品表</span><br>    category <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,     <span class="hljs-comment">-- 商品种类</span><br>    id <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,           <span class="hljs-comment">-- 商品id</span><br>    price <span class="hljs-built_in">DECIMAL</span>, <br>    <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>(category, id)  <span class="hljs-comment">-- 主键是 (category, id)</span><br>)   <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer (   <span class="hljs-comment">-- 客户表</span><br>    id <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,      <span class="hljs-comment">-- 客户id</span><br>    <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (id)      <span class="hljs-comment">-- 主键是 id</span><br>)   <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> product_order (         <span class="hljs-comment">-- 订单表</span><br>    <span class="hljs-keyword">no</span> <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT,  <span class="hljs-comment">-- number，自增长</span><br>    product_category <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,   <span class="hljs-comment">-- 商品种类</span><br>    product_id <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,         <span class="hljs-comment">-- 商品id </span><br>    customer_id <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,        <span class="hljs-comment">-- 客户id</span><br><br>    <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>(<span class="hljs-keyword">no</span>),                        <span class="hljs-comment">-- 主键是  no </span><br>    <span class="hljs-keyword">INDEX</span> (product_category, product_id),   <span class="hljs-comment">-- 对 (product_category, product_id) 做索引</span><br>    <span class="hljs-keyword">INDEX</span> (customer_id),                    <span class="hljs-comment">-- 对 customer_id 做索引</span><br><br>    <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span> (product_category, product_id)  <span class="hljs-comment">-- 两个外键约束</span><br>    <span class="hljs-keyword">REFERENCES</span> product(category, id)   <span class="hljs-comment">-- 字段 product_category 引用自 product表的category</span><br>                                       <span class="hljs-comment">-- 字段 product_id 引用自 product表的id</span><br>      <span class="hljs-keyword">ON</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">CASCADE</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">RESTRICT</span>,     <span class="hljs-comment">-- 级联跟新  和  严格模式删除</span><br><br>    <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span> (customer_id)<br>      <span class="hljs-keyword">REFERENCES</span> customer(id)<br>)   <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span>;</span><br></code></pre>

</div><div id="wmd-preview-section-13155" class="wmd-preview-section preview-content">

<h3 id="2-外键操作">2. 外键操作</h3>

</div><div id="wmd-preview-section-13656" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 表结构 摘自  MySQL 官方文档</span><br><span class="hljs-comment">--</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> parent (<br>    -&gt;     id <span class="hljs-built_in">int</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br>    -&gt;     <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span> (id)<br>    -&gt; ) <span class="hljs-keyword">engine</span>=<span class="hljs-keyword">innodb</span>;</span><br>Query OK, 0 rows affected (0.14 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> child (<br>    -&gt;     id <span class="hljs-built_in">int</span>, <br>    -&gt;     parent_id <span class="hljs-built_in">INT</span>,<br>    -&gt;     <span class="hljs-keyword">index</span> par_ind (parent_id),<br>    -&gt;     <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> (parent_id) <br>    -&gt;         <span class="hljs-keyword">references</span> parent(id)<br>    -&gt;         <span class="hljs-keyword">on</span> <span class="hljs-keyword">delete</span> <span class="hljs-keyword">cascade</span> <span class="hljs-keyword">on</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">cascade</span>  <span class="hljs-comment">-- 比官网例子增加 update cascade </span><br>    -&gt; ) <span class="hljs-keyword">engine</span>=<span class="hljs-keyword">innodb</span>;</span><br>Query ok, 0 rows affected (0.15 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> child <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 我们插入一条数据，id=1，parent_id=1</span><br>ERROR 1452 (23000): Cannot add or <span class="hljs-operator"><span class="hljs-keyword">update</span> a child <span class="hljs-keyword">row</span>: a <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> <span class="hljs-keyword">constraint</span> fails (<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`child`</span>, <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`child_ibfk_1`</span> <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`parent_id`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`parent`</span> (<span class="hljs-string">`id`</span>) <span class="hljs-keyword">ON</span> <span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">CASCADE</span>)  <br><span class="hljs-comment">-- 直接报错了，因为此时parent表中没有任何记录</span><br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> parent <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>);</span>   <span class="hljs-comment">-- 现在parent中插入记录</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> child <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 然后在child中插入记录，且parent_id是在parent中存在的</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> child <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">2</span>);</span>  <span class="hljs-comment">-- 插入parent_id=2的记录，报错。因为此时parent_id=2的记录不存在</span><br>ERROR 1452 (23000): Cannot add or <span class="hljs-operator"><span class="hljs-keyword">update</span> a child <span class="hljs-keyword">row</span>: a <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> <span class="hljs-keyword">constraint</span> fails (<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`child`</span>, <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`child_ibfk_1`</span> <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`parent_id`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`parent`</span> (<span class="hljs-string">`id`</span>) <span class="hljs-keyword">ON</span> <span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">CASCADE</span>)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span>  child;</span>     <br>+<span class="hljs-comment">------+-----------+</span><br>| id   | parent_id |<br>+<span class="hljs-comment">------+-----------+</span><br>|    1 |         1 |  <span class="hljs-comment">-- parent_id = 1</span><br>+<span class="hljs-comment">------+-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span>  parent;</span><br>+<span class="hljs-comment">----+</span><br>| id |<br>+<span class="hljs-comment">----+</span><br>|  1 |  <span class="hljs-comment">-- 根据表结构的定义（Foreign_key），这个值就是 child表中的id</span><br>+<span class="hljs-comment">----+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">update</span> parent <span class="hljs-keyword">set</span> id=<span class="hljs-number">100</span> <span class="hljs-keyword">where</span> id=<span class="hljs-number">1</span>;</span>       <br>Query OK, 1 row affected (0.04 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> parent;</span><br>+<span class="hljs-comment">-----+</span><br>| id  |<br>+<span class="hljs-comment">-----+</span><br>| 100 |  <span class="hljs-comment">-- 已经设置成了100</span><br>+<span class="hljs-comment">-----+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> child;</span><br>+<span class="hljs-comment">------+-----------+</span><br>| id   | parent_id |<br>+<span class="hljs-comment">------+-----------+</span><br>|    1 |       100 |  <span class="hljs-comment">-- 自动变化，这是on update cascade的作用，联级更新，parent更新，child也跟着更新</span><br>+<span class="hljs-comment">------+-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> parent <span class="hljs-keyword">where</span> id=<span class="hljs-number">100</span>;</span>  <span class="hljs-comment">-- 删除这条记录</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> parent;</span>  <span class="hljs-comment">-- id=100的记录已经被删除了</span><br>Empty <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> child;</span>  <span class="hljs-comment">-- id=1，parent_id=100的记录跟着被删除了。on delete cascade的作用</span><br>Empty <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> child <span class="hljs-keyword">drop</span> <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> child_ibfk_1;</span>  <span class="hljs-comment">-- 删除 之前的外键</span><br>Query OK, 0 rows affected (0.07 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> child <span class="hljs-keyword">add</span> <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span>(parent_id) <br>    -&gt; <span class="hljs-keyword">references</span> parent(id) <span class="hljs-keyword">on</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">cascade</span> <span class="hljs-keyword">on</span> <span class="hljs-keyword">delete</span> <span class="hljs-keyword">restrict</span>;</span>  <span class="hljs-comment">-- 使用严格模式</span><br>Query OK, 0 rows affected (0.27 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> parent <span class="hljs-keyword">values</span>(<span class="hljs-number">50</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> child <span class="hljs-keyword">values</span>(<span class="hljs-number">3</span>,<span class="hljs-number">50</span>);</span> <br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> child <span class="hljs-keyword">values</span>(<span class="hljs-number">3</span>,<span class="hljs-number">51</span>);</span>  <span class="hljs-comment">-- 和之前一样会提示错误</span><br>ERROR 1452 (23000): Cannot add or <span class="hljs-operator"><span class="hljs-keyword">update</span> a child <span class="hljs-keyword">row</span>: a <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> <span class="hljs-keyword">constraint</span> fails (<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`child`</span>, <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`child_ibfk_1`</span> <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`parent_id`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`parent`</span> (<span class="hljs-string">`id`</span>) <span class="hljs-keyword">ON</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">CASCADE</span>)<br><br>mysql&gt; <span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> parent <span class="hljs-keyword">where</span> id=<span class="hljs-number">50</span>;</span>  <span class="hljs-comment">-- 删除失败了，因为是restrict模式</span><br>ERROR 1451 (23000): Cannot <span class="hljs-operator"><span class="hljs-keyword">delete</span> <span class="hljs-keyword">or</span> <span class="hljs-keyword">update</span> a parent <span class="hljs-keyword">row</span>: a <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> <span class="hljs-keyword">constraint</span> fails (<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`child`</span>, <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`child_ibfk_1`</span> <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`parent_id`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`parent`</span> (<span class="hljs-string">`id`</span>) <span class="hljs-keyword">ON</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">CASCADE</span>)<br><br><span class="hljs-comment">-- 注意，delete 后面说明都不写表示 no action == restrict</span></span><br></code></pre>

<blockquote>
  <p>外键约束，可以让数据进行一致性更新，但是会有一定的<code>性能损耗</code>，线上业务使用不多。通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。</p>
</blockquote></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>